import time
from . import ConnPG
import psycopg2
from sanic.log import logger


class OddTable:

    # 添加一个定单
    async def add_odd(self, in_or_out, subject_id, sponsor_id, warehouse_id, remark):  # 添加库房

        # 参数检查


        if not all([in_or_out, subject_id, sponsor_id, warehouse_id]):

            return {
                'status': -1,
                'errorCode': 303100,
                'errorMsg': '检查参数',
                'resultBody': None
            }

        sql = '''\
        INSERT INTO odd(
            in_or_out, 
            subject_id, 
            sponsor_id, 
            warehouse_id, 
            remark,
            create_time
            ) 
        VALUES (
            %s, %s, %s, %s, %s, %s) RETURNING id'''

        try:
            result = await ConnPG().fetchone(
                sql,
                (in_or_out, subject_id, sponsor_id,
                 warehouse_id, remark, int(time.time()))

            )
        except psycopg2.errors.ForeignKeyViolation as e:

            return {
                'status': -1,
                'errorCode': 303101,
                'errorMsg': str(e),
                'resultBody': None,
            }
        else:

            return {
                'status': 1,
                'errorCode': None,
                'errorMsg': None,
                'resultBody': result,
            }

    # 库房人员接受订单
    async def receive_odd(self, odd_id, operator_id):

        check_sql = 'select * from odd where id=%s'

        check_info = await ConnPG().fetchone(check_sql, (odd_id,))

        if check_info == None:  # 订单不存在

            return {
                'status': -1,
                'errorCode': 303102,
                'errorMsg': '没有该订单',
                'resultBody': None,
            }

        # 订单已经被其他工作人员接受
        elif check_info['operator_id'] != int(operator_id) and check_info['operator_id'] != None:

            return {
                'status': -1,
                'errorCode': 303103,
                'errorMsg': '订单已经被其他工作人员接受',
                'resultBody': None,
            }

        # 你已经是该订单的操作人员
        elif check_info['operator_id'] == int(operator_id):

            return {
                'status': 1,
                'errorCode': None,
                'errorMsg': None,
                'resultBody': '你已经是该订单的操作员',
            }

        # 设置你为该订单的操作人员
        elif check_info['operator_id'] == None:

            sql = 'update odd set operator_id=%s where id=%s'

            await ConnPG().execute(sql, (operator_id, odd_id))

            return {
                'status': 1,
                'errorCode': None,
                'errorMsg': None,
                'resultBody': '设置你为该订单的操作人员',
            }

    # 库房人员完成订单
    async def success_odd(self, odd_id):

        sql = "update odd set status='success',end_time=%s where id=%s"
        check_sql = "select * from odd where id=%s"

        check_info = await ConnPG().fetchone(check_sql, (odd_id,))

        if check_info == None:  # 订单不存在

            return {
                'status': -1,
                'errorCode': 303104,
                'errorMsg': '没有该订单',
                'resultBody': None,
            }

        elif check_info['status'] == 'success':

            return {
                'status': 1,
                'errorCode': None,
                'errorMsg': None,
                'resultBody': '订单已经是完成状态',
            }

        elif check_info['status'] == 'delete':

            return {
                'status': -1,
                'errorCode': 303105,
                'errorMsg': '订单已经被删除不可逆，请重新组织订单',
                'resultBody': None,
            }

        else:

            await ConnPG().execute(sql, (int(time.time()), odd_id))

            return {
                'status': 1,
                'errorCode': None,
                'errorMsg': None,
                'resultBody': '订单修改为完成状态',
            }

    # 订单remark修改
    async def change_remark(self, odd_id, remark):

        sql = "update odd set remark=%s where id=%s"
        check_sql = "select * from odd where id=%s"

        check_info = await ConnPG().fetchone(check_sql, (odd_id,))

        if check_info == None:  # 订单不存在

            return {
                'status': -1,
                'errorCode': 303107,
                'errorMsg': '没有该订单',
                'resultBody': None,
            }

        await ConnPG().execute(sql, (remark, odd_id))
        return {
            'status': 1,
            'errorCode': None,
            'errorMsg': None,
            'resultBody': '订单说明更新成功',
        }

    # select_odd_by_status
    async def select_odd_by_status(self, status, page):

        if page == None:

            page = 1

        elif page.isdecimal():

            page = int(page)

        else:

            return {
                'status': -1,
                'errorCode': 303107,
                'errorMsg': '请检查参数合法性',
                'resultBody': None
            }

        sql = "select * from odd where status=%s limit 20 offset %s"

        result = await ConnPG().fetchall(sql, (status, (page-1)*20))

        return {
            'status': 1,
            'errorCode': None,
            'errorMsg': None,
            'resultBody': result,
        }


    async def select_all_odd(self):

        sql = "select * from view_odd"

        result = await ConnPG().fetchall(sql)

        return {
            'status': 1,
            'errorCode': None,
            'errorMsg': None,
            'resultBody': result,
        }

    async def select_all_odd_by_duty(self, duty_id):

        if duty_id == None or not duty_id.isdecimal():

            return {
                'status': -1,
                'errorCode': 303107,
                'errorMsg': '却少duty_id参数',
                'resultBody': None
            }



        sql_ = "select * from view_duty_warehouse where duty_id=%s"

        duty_result = await ConnPG().fetchall(sql_, (duty_id,))

        warehouse_list = [i['warehouse_id'] for i in duty_result]

        warehouse_list_str = ''

        for i in warehouse_list:

            warehouse_list_str = warehouse_list_str + f'or warehouse_id={i} '

        sql = "select * from view_odd where " + warehouse_list_str[2:]

        result = await ConnPG().fetchall(sql)

        result_ = []

        for i in result:


            warehouse_id = i['warehouse_id']

            for ii in duty_result:

                if ii['warehouse_id'] == warehouse_id:

                    permission = ii

                    break
            
            if permission['in_odd'] == True :

                if i['in_or_out'] == True:
                    result_.append(i)
            
            if permission['out_odd'] == True:

                if i['in_or_out'] == False:
                    result_.append(i)


        return {
            'status': 1,
            'errorCode': None,
            'errorMsg': None,
            'resultBody': result_,
        }

    
    #订单的详细情况
    async def odd_detail(self, odd_id):

        #订单总体情况
        sql = 'select * from view_odd where id=%s'
        result = await ConnPG().fetchone(sql, (odd_id,))

        #订单包含的sku列表
        sql_ = 'select * from view_sku_account where odd_id=%s'
        result_ = await ConnPG().fetchall(sql_, (odd_id,))

        # sku_id_list = [i['sku_id'] for i in result_]

        #找到每个sku属性，sku详情
        for sku in result_:

            sku_id = sku['sku_id']

            sql_sku_info = 'select * from view_sku_info where id=%s'

            sku_info = await ConnPG().fetchall(sql_sku_info, (sku_id,))



            sku['sku_info'] = sku_info

            sql_sku_attribute = 'select * from view_sku_attribute where sku_id=%s'

            sku_attribute = await ConnPG().fetchall(sql_sku_attribute, (sku_id,))

            attribute_str = ''
            for i in sku_attribute:

                attribute_str = attribute_str + '|' + i['attribute_key'] + ':' + i['attribute_value']

            sku['attribute_str'] = attribute_str[1:]



        return {
            'status': 1,
            'errorCode': None,
            'errorMsg': None,
            'resultBody': {'odd_detail': result, 'sku_detail': result_},
        }
    #删除订单
    async def del_odd(self, odd_id):

        sql = 'DELETE FROM sku_account where odd_id=%s'

        result = await ConnPG().execute(sql, (odd_id,))

        sql_ = 'DELETE FROM odd WHERE id=%s'  
        result1 = await ConnPG().execute(sql_, (odd_id,))

        return {
            'status': 1,
            'errorCode': None,
            'errorMsg': None,
            'resultBody': '删除成功',
        }















